CREATE OR REPLACE PACKAGE BODY manage_dept_proj
AS
  PROCEDURE allocate_new_proj_list
    (p_dept_id NUMBER, p_name VARCHAR2, p_budget NUMBER)
  IS
    v_accounting_project typ_projectlist; 
  BEGIN   -- this example uses a constructor
    v_accounting_project :=
      typ_ProjectList
        (typ_Project (1, 'Dsgn New Expense Rpt', 3250),
         typ_Project (2, 'Outsource Payroll', 12350),
         typ_Project (3, 'Audit Accounts Payable',1425));
    INSERT INTO department VALUES
      (p_dept_id, p_name, p_budget, v_accounting_project);
  END allocate_new_proj_list;

  FUNCTION get_dept_project (p_dept_id NUMBER)
    RETURN typ_projectlist
  IS
    v_accounting_project typ_projectlist;
  BEGIN
   -- this example uses a fetch from the database
     SELECT  projects
       INTO  v_accounting_project
       FROM  department
       WHERE dept_id = p_dept_id;
     RETURN v_accounting_project;
  END get_dept_project;

PROCEDURE update_a_project 
    (p_deptno NUMBER, p_new_project typ_Project, 
     p_position NUMBER)
  IS
     v_my_projects typ_ProjectList;
  BEGIN
    v_my_projects := get_dept_project (p_deptno);
    v_my_projects.EXTEND;   --make room for new project
    /* Move varray elements forward */
    FOR i IN REVERSE p_position..v_my_projects.LAST - 1 LOOP
      v_my_projects(i + 1) := v_my_projects(i);
    END LOOP;
    v_my_projects(p_position) := p_new_project; -- add new
                                                -- project
    UPDATE department SET projects = v_my_projects
      WHERE dept_id = p_deptno;
  END update_a_project;
  
  FUNCTION manipulate_project (p_dept_id NUMBER)
    RETURN typ_projectlist
  IS
    v_accounting_project typ_projectlist;
    v_changed_list typ_projectlist;
  BEGIN
    SELECT  projects
       INTO  v_accounting_project
       FROM  department
       WHERE dept_id = p_dept_id; 
  -- this example assigns one collection to another
    v_changed_list := v_accounting_project;
    RETURN v_changed_list;
  END manipulate_project;

  FUNCTION check_costs (p_project_list typ_projectlist)
    RETURN boolean
  IS
    c_max_allowed        NUMBER := 10000000;
    i                    INTEGER;
    v_flag               BOOLEAN := FALSE;
  BEGIN
    i := p_project_list.FIRST ; 
    WHILE i IS NOT NULL LOOP
      IF p_project_list(i).cost > c_max_allowed then
        v_flag := TRUE;
        dbms_output.put_line (p_project_list(i).title ||
                         ' exceeded allowable budget.');
        RETURN TRUE;
      END IF;
    i := p_project_list.NEXT(i); 
    END LOOP;
    RETURN null;
  END check_costs;

END manage_dept_proj;
